package nl.saxion.act.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import nl.saxion.act.db.DatabaseManager;
import nl.saxion.act.wt.Auction;


public class AuctionDAO {

	private String sqlURLread   = "SELECT * FROM Auction ORDER BY endTime DESC FETCH FIRST 5 ROWS ONLY" ;
	private String sqlURLread1   = "SELECT * FROM Auction ORDER BY endTime DESC" ;
	private String sqlAuctionCatagory = "SELECT * FROM Auction WHERE catagory = ? ORDER BY endTime DESC " ;
    private String sqlURLdelete = "DELETE FROM Auction where auctionID = ?";
	private String sqlURLcreate = "INSERT INTO Auction (title, description, price, endTime, auctionStarterID, catagory) VALUES(?, ?, ?, ?, ?, ?)";
	private String sqlURLupdate = "UPDATE Auction SET price = ? WHERE auctionID = ?";
	private String sqlAuctionID = "SELECT * FROM Auction WHERE auctionID = ?";
	private String count		= "SELECT Count(*) FROM Auction";
	
	private DatabaseManager myDb = null;
	private Connection        con      = null ;
	private PreparedStatement psRead   = null ;
	private PreparedStatement psRead1   = null ;
	private PreparedStatement psCatagory = null;
	private PreparedStatement psDelete = null ;
	private PreparedStatement psCreate = null ;
	private PreparedStatement psUpdate = null ;
	private PreparedStatement psID = null ;
	private PreparedStatement psCount 	= null;
	private int countAuctions          = 0;
	
	public AuctionDAO(DatabaseManager myDb){
		
		this.myDb = myDb;
		con = myDb.getConnection();
		
		try{
			
			this.psRead   = con.prepareStatement(sqlURLread) ;
			this.psRead1   = con.prepareStatement(sqlURLread1) ;
			this.psCatagory = con.prepareStatement(sqlAuctionCatagory);
		    this.psDelete = con.prepareStatement(sqlURLdelete);
		    this.psCreate = con.prepareStatement(sqlURLcreate);
		    this.psUpdate = con.prepareStatement(sqlURLupdate);
		    this.psID 		= con.prepareStatement(sqlAuctionID);
		    this.psCount	= con.prepareStatement(count);
		    
		    Statement stmt     = con.createStatement() ;
			ResultSet rs       = stmt.executeQuery("SELECT count(*) FROM Auction") ;
			
			if (rs.next()) {
				countAuctions = rs.getInt(1) ;
			}
			
			rs.close() ;
			stmt.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;

		}
	}
	
	public ArrayList<Auction> getAllAuctions(){
		ArrayList<Auction> auctions = new ArrayList<Auction>();

		try{
			ResultSet rs = psRead1.executeQuery();
			
			while (rs.next()) {
				int auctionID      		= rs.getInt		("auctionID");
				String title  	   		= rs.getString	("title");
				String description		= rs.getString	("description");
				double price	  	 	= rs.getDouble	("price");
				Timestamp endTime	= rs.getTimestamp("endTime");
				int auctionStarterID	= rs.getInt		("auctionStarterID");
				String catagory			= rs.getString	("catagory");
				
				Auction a = new Auction(title, description, catagory, price, endTime, auctionStarterID); 
				a.setAuctionID(auctionID);
				a.setAllBids(new BiddingDAO(myDb).getBidFromAuctions(auctionID));
				auctions.add(a);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}

		return auctions;
	}	
	
	public ArrayList<Auction> getFirstFewAuctions(){
		ArrayList<Auction> auctions = new ArrayList<Auction>();

		try{
			ResultSet rs = psRead.executeQuery();
			
			while (rs.next()) {
				int auctionID      		= rs.getInt		("auctionID");
				String title  	   		= rs.getString	("title");
				String description		= rs.getString	("description");
				double price	  	 	= rs.getDouble	("price");
				Timestamp endTime	= rs.getTimestamp	("endTime");
				int auctionStarterID	= rs.getInt		("auctionStarterID");
				String catagory			= rs.getString	("catagory");
				
				Auction a = new Auction(title, description, catagory, price, endTime, auctionStarterID); 
				a.setAuctionID(auctionID);
				a.setAllBids(new BiddingDAO(myDb).getBidFromAuctions(auctionID));
				auctions.add(a);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			se.printStackTrace();
			getAllAuctions();
		}

		return auctions;
	}
	
	public ArrayList<Auction> getAuctionsCatagory(String catagory){
		ArrayList<Auction> auctions = new ArrayList<Auction>();

		try{
			psCatagory.clearParameters();
			
			psCatagory.setString(1, catagory);
			
			ResultSet rs = psCatagory.executeQuery();
			
			while (rs.next()) {
				int auctionID      		= rs.getInt		("auctionID");
				String title  	   		= rs.getString	("title");
				String description		= rs.getString	("description");
				double price	  	 	= rs.getDouble	("price");
				Timestamp endTime	= rs.getTimestamp	("endTime");
				int auctionStarterID	= rs.getInt		("auctionStarterID");
				String catagory1			= rs.getString	("catagory");
				
				Auction a = new Auction(title, description, catagory1, price, endTime, auctionStarterID); 
				a.setAuctionID(auctionID);
				a.setAllBids(new BiddingDAO(myDb).getBidFromAuctions(auctionID));
				auctions.add(a);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}

		return auctions;
	}	
	
	public Auction getAuctionID(int id){
		Auction a = null;

		try{
			psID.clearParameters();
			
			psID.setInt(1, id);
			
			ResultSet rs = psID.executeQuery();

			while(rs.next()){
				int auctionID      		= rs.getInt		("auctionID");
				String title  	   		= rs.getString	("title");
				String description		= rs.getString	("description");
				double price	  	 	= rs.getDouble	("price");
				Timestamp endTime		= rs.getTimestamp	("endTime");
				int auctionStarterID	= rs.getInt		("auctionStarterID");
				String catagory1		= rs.getString	("catagory");
				
				a = new Auction(title, description, catagory1, price, endTime, auctionStarterID); 
				a.setAuctionID(auctionID);
				a.setAllBids(new BiddingDAO(myDb).getBidFromAuctions(auctionID));

			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}

		return a;
	}	
	
	public Auction insertAuction(String title, String description,
			double price, Timestamp endTime, int auctionStarterID,
			String catagory) {
		try {
			this.psCreate = con.prepareStatement(sqlURLcreate);
			
			psCreate.clearParameters();

			psCreate.setString(1, title);
			psCreate.setString(2, description);
			psCreate.setDouble(3, price);
			psCreate.setTimestamp(4, endTime);
			psCreate.setInt(5, auctionStarterID);
			psCreate.setString(6, catagory);
			
			psCreate.executeUpdate();
			
			ResultSet rs = psCount.executeQuery();
			while(rs.next()){
				countAuctions = rs.getInt(1);
			}
			
			return getAuctionID(countAuctions);
			
		} catch (SQLException se) {
			printSQLException(se);
			se.printStackTrace();
			return null;
		}

	}
	
	public void deleteAuction(int auctionID){
		try {
			psDelete.clearParameters();
			psDelete.setInt(1, auctionID);
			psDelete.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void updatePrice(int auctionID, double price){
		try {
			psUpdate.clearParameters();
			psUpdate.setDouble(1, price);
			psUpdate.setInt(2, auctionID);
			psUpdate.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void printSQLException(SQLException se) {
//		while(se != null) {
//
//			System.out.print("SQLException: State:   " + se.getSQLState());
//			System.out.println("Severity: " + se.getErrorCode());
//			System.out.println(se.getMessage());			
//
//			se = se.getNextException();
//		}
		se.printStackTrace();
	}

}
